﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class KetQuaDAO
    {
        #region lay tat ca ket qua
        public static List<KetQuaDTO> getAllResults()
        {
            SqlConnection connect = null;
            List<KetQuaDTO> list = new List<KetQuaDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from ketqua";
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    KetQuaDTO kq = new KetQuaDTO();
                    kq.Id = reader.GetInt32(0);
                    kq.NguoiTraLoi = reader.GetInt32(1);
                    kq.CauHoi = reader.GetInt32(2);
                    kq.KetQua = reader.GetInt32(3);
                    kq.NgayTraLoi = reader.GetDateTime(4);
                    list.Add(kq);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region lay ket qua theo ma
        public static KetQuaDTO getResultById(int id)
        {
            SqlConnection connect = null;
            KetQuaDTO kq = new KetQuaDTO();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from ketqua where id="+id;
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    kq.Id = reader.GetInt32(0);
                    kq.NguoiTraLoi = reader.GetInt32(1);
                    kq.CauHoi = reader.GetInt32(2);
                    kq.KetQua = reader.GetInt32(3);
                    kq.NgayTraLoi = reader.GetDateTime(4);
                }
            }
            catch (Exception ex)
            {
                kq = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return kq;
        }
        #endregion

        #region lay ket qua theo tai khoan
        public static List<KetQuaDTO> getResultsByAccountId(int maTaiKhoan)
        {
            SqlConnection connect = null;
            List<KetQuaDTO> list = new List<KetQuaDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from ketqua where nguoitraloi=" + maTaiKhoan;
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    KetQuaDTO kq = new KetQuaDTO();
                    kq.Id = reader.GetInt32(0);
                    kq.NguoiTraLoi = reader.GetInt32(1);
                    kq.CauHoi = reader.GetInt32(2);
                    kq.KetQua = reader.GetInt32(3);
                    kq.NgayTraLoi = reader.GetDateTime(4);
                    list.Add(kq);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region lay ket qua theo ma cau hoi
        public static List<KetQuaDTO> getResultsByQuestionId(int maCauHoi)
        {
            SqlConnection connect = null;
            List<KetQuaDTO> list = new List<KetQuaDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from ketqua where cauhoi=" + maCauHoi;
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    KetQuaDTO kq = new KetQuaDTO();
                    kq.Id = reader.GetInt32(0);
                    kq.NguoiTraLoi = reader.GetInt32(1);
                    kq.CauHoi = reader.GetInt32(2);
                    kq.KetQua = reader.GetInt32(3);
                    kq.NgayTraLoi = reader.GetDateTime(4);
                    list.Add(kq);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region them ket qua
        public static bool insertResult(KetQuaDTO kq)
        {
            bool result = true;
            SqlConnection connect = null;
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "insert into ketqua(nguoitraloi, cauhoi, ketqua, ngaytraloi"
                + " values(@nguoitraloi, @cauhoi, @ketqua, @ngaytraloi)";
                SqlCommand command = new SqlCommand(strCommand, connect);

                SqlParameter param;
                param = new SqlParameter("@nguoitraloi", SqlDbType.Int);
                param.Value = kq.NguoiTraLoi;
                command.Parameters.Add(param);

                param = new SqlParameter("@cauhoi", SqlDbType.Int);
                param.Value = kq.CauHoi;
                command.Parameters.Add(param);

                param = new SqlParameter("@ketqua", SqlDbType.Int);
                param.Value = kq.KetQua;
                command.Parameters.Add(param);

                param = new SqlParameter("@ngaytraloi", SqlDbType.DateTime);
                param.Value = kq.NgayTraLoi;
                command.Parameters.Add(param);

                int n = command.ExecuteNonQuery();
                if (n == 0)
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return result;
        }
        #endregion
    }
}
